package ch.sv7.tool.dbmanager.db.ant;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.apache.tools.ant.AntClassLoader;
import org.apache.tools.ant.Task;
import org.apache.tools.ant.types.Path;
import org.apache.tools.ant.types.Reference;

import ch.sv7.tool.dbmanager.DbManagerException;
import ch.sv7.tool.log.AntAppender;
import ch.sv7.tool.log.Appender;
import ch.sv7.tool.log.Logger;

public class SqlTask extends Task {

	private String driver;

	private String url;

	private String user;

	private String password;

	private Path classpath;

	private AntClassLoader loader;

	private String rdbms;

	private String version;

	private String preSql;

	private String postSql;

	public SqlTask() {
		Logger.setAppender(new AntAppender(this, Appender.LEVEL_INFO));
		rdbms = null;
		version = null;
	}

	public void setDriver(String driver) {
		this.driver = driver;
	}

	public void setUrl(String url) {
		this.url = url;
	}

	public void setUser(String user) {
		this.user = user;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public void setClasspath(Path classpath) {
		if (this.classpath == null)
			this.classpath = classpath;
		else
			this.classpath.append(classpath);
	}

	public Path createClasspath() {
		if (classpath == null)
			classpath = new Path(getProject());
		return classpath.createPath();
	}

	public void setClasspathRef(Reference r) {
		createClasspath().setRefid(r);
	}

	protected Connection getConnection() throws DbManagerException {
		Driver driverInstance = null;
		try {
			Class dc;
			if (classpath != null) {
				log("Loading " + driver + " using AntClassLoader with classpath " + classpath, 3);
				loader = new AntClassLoader(getProject(), classpath);
				dc = loader.loadClass(driver);
			} else {
				log("Loading " + driver + " using system loader.", 3);
				dc = Class.forName(driver);
			}
			driverInstance = (Driver) dc.newInstance();
		} catch (ClassNotFoundException e) {
			throw new DbManagerException("Class Not Found: JDBC driver " + driver + " could not be loaded", e);
		} catch (IllegalAccessException e) {
			throw new DbManagerException("Illegal Access: JDBC driver " + driver + " could not be loaded", e);
		} catch (InstantiationException e) {
			throw new DbManagerException("Instantiation Exception: JDBC driver " + driver + " could not be loaded", e);
		}
		Connection conn = null;
		log("connecting to " + url, 3);
		Properties info = new Properties();
		info.put("user", user);
		info.put("password", password);
		try {
			conn = driverInstance.connect(url, info);
			conn.setAutoCommit(true);
			if (conn == null)
				throw new SQLException("No suitable Driver for " + url);
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			throw new DbManagerException("Unable to get connection to URL : " + url + " user : " + user, e);
		}

		if (preSql != null && preSql.trim().length() > 0) {
			try {
				executeSql(conn, preSql);
			} catch (SQLException e) {
				throw new DbManagerException("Unable to execute preSql statement : " + preSql);
			}
		}

		return conn;
	}

	protected void executeSql(Connection conn, String sql) throws SQLException {
		Statement stmt = conn.createStatement();
		stmt.execute(sql);
		stmt.close();
	}

	protected void closeConnection(Connection conn) throws DbManagerException {
		if (conn != null)
			try {
				if (postSql != null && postSql.trim().length() > 0) {
					executeSql(conn, postSql);
				}
				conn.close();
			} catch (SQLException e) {
				throw new DbManagerException("Unable to close connection to DB", e);
			}
	}

	protected boolean isValidRdbms(Connection conn) throws DbManagerException {
		if (rdbms == null && version == null)
			return true;
		try {
			DatabaseMetaData dmd = conn.getMetaData();
			if (rdbms != null) {
				String theVendor = dmd.getDatabaseProductName().toLowerCase();
				log("RDBMS = " + theVendor, 3);
				if (theVendor == null || theVendor.indexOf(rdbms) < 0) {
					log("Not the required RDBMS: " + rdbms, 3);
					return false;
				}
			}
			if (version != null) {
				String theVersion = dmd.getDatabaseProductVersion().toLowerCase();
				log("Version = " + theVersion, 3);
				if (theVersion == null || !theVersion.startsWith(version) && theVersion.indexOf(" " + version) < 0) {
					log("Not the required version: \"" + version + "\"", 3);
					return false;
				}
			}
		} catch (SQLException e) {
			log("Failed to obtain required RDBMS information", 0);
			return false;
		}
		return true;
	}

	public String getPreSql() {
		return preSql;
	}

	public void setPreSql(String preSql) {
		this.preSql = preSql;
	}

	public String getPostSql() {
		return postSql;
	}

	public void setPostSql(String postSql) {
		this.postSql = postSql;
	}

}
